-- 销量，总金额，普通优惠
-- DROP TABLE IF EXISTS `mid_coupon_all`;
create  table mid_coupon_all (
   `day` varchar(20) ,
   `station_id` int ,
   `oils` varchar(15) ,
   `oilMoney` decimal(19,2) ,
   `oilLitre` decimal(19,2) ,
   `ordinary_discount` decimal(19,2)
)
PARTITIONED BY (dt STRING )
STORED AS PARQUET  TBLPROPERTIES('parquet.compression'='SNAPPY');

-- DROP TABLE IF EXISTS `mid_coupon_oil_order`;
CREATE TABLE `mid_coupon_oil_order`(
  `pay_type` varchar(250),
  `oil_type` varchar(10),
  `c_id` varchar(50),
  `out_shop_id` varchar(50),
  `day` varchar(30)
)
PARTITIONED BY (dt STRING )
STORED AS PARQUET  TBLPROPERTIES('parquet.compression'='SNAPPY');

-- 油和非油
CREATE TABLE  mid_oilnotoil(
  `day` varchar(30),
  `use_shop_id` varchar(50),
  `productcode` varchar(10),
  `oil_usedmoney`  decimal(19,2) ,
  `notoil_usedmoney`  decimal(19,2)
)
PARTITIONED BY (dt STRING )
STORED AS PARQUET  TBLPROPERTIES('parquet.compression'='SNAPPY');

-- 直降
CREATE TABLE  mid_direct(
  `day` varchar(30),
  `use_shop_id` varchar(50),
  `productcode` varchar(10),
  `direct_discount`  decimal(19,2)
)
PARTITIONED BY (dt STRING )
STORED AS PARQUET  TBLPROPERTIES('parquet.compression'='SNAPPY');

DROP TABLE IF EXISTS `res_coupon_sum`;
CREATE TABLE `res_coupon_sum`(
  `day` varchar(20),
  `station_id` int,
  `oils` varchar(15),
  `oilMoney` decimal(19,2),
  `oilLitre` decimal(19,2),
  `direct_discount` decimal(19,2),
  `oil_discount` decimal(19,2),
  `notoil_discount` decimal(19,2),
  `ordinary_discount` decimal(19,2))
PARTITIONED BY (
  `dt` string)
ROW FORMAT DELIMITED
  FIELDS TERMINATED BY ','
STORED AS INPUTFORMAT
  'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat';

create  table mid_ordinary_discount (
   `day` varchar(20) ,
   `station_id` int ,
   `oils` varchar(15) ,
   `sum_total` decimal(19,2) ,
   `sum_sales` decimal(19,2) ,
   `ordinary_discount` decimal(19,2)
)
PARTITIONED BY (dt STRING )
STORED AS PARQUET  TBLPROPERTIES('parquet.compression'='SNAPPY');


DROP TABLE IF EXISTS `res_oilb`;
CREATE TABLE `res_oilb`(
  `minutes` varchar(40),
  `stationid` int,
  `number92` int,
  `litre92` double,
  `money92` double,
  `number95` int,
  `litre95` double,
  `money95` double,
  `number97` int,
  `litre97` double,
  `money97` double,
  `number0` int,
  `litre0` double,
  `money0` double,
  `numbe_10` int,
  `litre_10` double,
  `money_10` double,
  `number_20` int,
  `litre_20` double,
  `money_20` double,
  `number98` int,
  `litre98` double,
  `money98` double,
  `number80` int,
  `litre80` double,
  `money80` double,
  `number_35` int,
  `litre_35` double,
  `money_35` double
  )
ROW FORMAT DELIMITED
  FIELDS TERMINATED BY ','
STORED AS INPUTFORMAT
  'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  'hdfs://hadoop1:9000/user/hive/warehouse/res_oilb';

DROP TABLE IF EXISTS `res_oilb_vip`;
CREATE TABLE `res_oilb_vip`(
  `minutes` varchar(40),
  `stationid` int,
  `number92` int,
  `litre92` double,
  `money92` double,
  `number95` int,
  `litre95` double,
  `money95` double,
  `number97` int,
  `litre97` double,
  `money97` double,
  `number0` int,
  `litre0` double,
  `money0` double,
  `numbe_10` int,
  `litre_10` double,
  `money_10` double,
  `number_20` int,
  `litre_20` double,
  `money_20` double,
  `number98` int,
  `litre98` double,
  `money98` double,
  `number80` int,
  `litre80` double,
  `money80` double,
  `number_35` int,
  `litre_35` double,
  `money_35` double
  )
ROW FORMAT DELIMITED
  FIELDS TERMINATED BY ','
STORED AS INPUTFORMAT
  'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  'hdfs://hadoop1:9000/user/hive/warehouse/res_oilb_vip';

DROP TABLE IF EXISTS `perjiayou`;
CREATE TABLE `perjiayou` (
   `dt` varchar(50),
   `totalnum` int,
   `totalnum7` int ,
   `vipnum` int,
   `vipnum7` int
 )
 ROW FORMAT DELIMITED
  FIELDS TERMINATED BY ','
STORED AS INPUTFORMAT
  'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat';
